# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os
import datetime
import math

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 基金列表对比（基金名称、所在地、详细地址、基金简介、基金类型、
                                                        资本来源、募集完成规模、开始募集时间、募集完成时间、募集状态、
                                                        是否基金业协会备案、管理公司、LP名称、LP类型、
                                                        投资伦次、投资策略、投资的企业）
1.0基金中没有联系电话 和 联系地址，有注册地址；没有成立时间、没有 基金网址
注：投资轮次 开发那边数据未迁移过来呢，原因是之前没有事件数据，之后会迁移
注：所在地 是天眼查的，已确认不用对了
注：投资的企业 已经去掉了不用对了
注：管理公司、LP名称、LP类型 已经去掉了不用对了
"""

def searchCvs1FundList():
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM dc_fund a WHERE a.`status`='1' ORDER BY a.id DESC"
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50

    # for index in range(pageCount):
    for index in range(6,994):
        limitStart = index * 50

        print("limitStart====================================================",limitStart)

        #基金名称、资本来源、基金类型、注册地址（详细地址）、基金简介、募集开始时间、募集完成时间、募集完成规模、募集状态
        sql = "SELECT a.id AS fundIdOrCompanyId, a.cn_name AS fundName, a.capital_from_type AS source, a.info_status, " \
              "a.fund_type AS fundType, c.reg_location AS address, c.cn_desc AS description, '' AS strategyDescribe, " \
              "b.start_time AS collectBeginTime, b.finally_time AS collectFinallyTime, " \
              "b.finally_scale AS collectFinallyScale, a.raise_status AS collectStatus, '' AS lp_data, " \
              "c.cn_short AS manager_company_short_names, ndrc_record_type AS fund_association, a.ext1 AS beizhu " \
              "FROM dc_fund a " \
              "LEFT JOIN dc_company c " \
              "ON a.company_id = c.id " \
              "LEFT JOIN dc_fund_collect b " \
              "ON b.fund_id = a.id " \
              "WHERE a.`status` = '1' " \
              "ORDER BY a.`cn_name` DESC LIMIT %d,50 "%limitStart

        # sql = "SELECT a.id AS fundIdOrCompanyId, a.cn_name AS fundName, a.capital_from_type AS source, a.info_status, " \
        #       "a.fund_type AS fundType, c.reg_location AS address, c.cn_desc AS description, '' AS strategyDescribe, " \
        #       "b.start_time AS collectBeginTime, b.finally_time AS collectFinallyTime, " \
        #       "b.finally_scale AS collectFinallyScale, a.raise_status AS collectStatus, '' AS lp_data, " \
        #       "c.cn_short AS manager_company_short_names, ndrc_record_type AS fund_association, a.ext1 AS beizhu " \
        #       "FROM datacenter_online_20180609.dc_fund a " \
        #       "LEFT JOIN dc_company c " \
        #       "ON a.company_id = c.id " \
        #       "LEFT JOIN dc_fund_collect b " \
        #       "ON b.fund_id = a.id " \
        #       "WHERE a.`status` = '1' AND a.`cn_name`='融源成长（天津）股权投资合伙企业（有限合伙）'"

        # sql = "SELECT a.id AS fundIdOrCompanyId, a.cn_name AS fundName, a.capital_from_type AS source, a.info_status, " \
        #       "a.fund_type AS fundType, c.reg_location AS address, c.cn_desc AS description, '' AS strategyDescribe, " \
        #       "b.start_time AS collectBeginTime, b.finally_time AS collectFinallyTime, " \
        #       "b.finally_scale AS collectFinallyScale, a.raise_status AS collectStatus, '' AS lp_data, " \
        #       "c.cn_short AS manager_company_short_names, a.ext1 AS beizhu " \
        #       "FROM datacenter_online_20180609.dc_fund a " \
        #       "LEFT JOIN dc_company c " \
        #       "ON a.company_id = c.id " \
        #       "LEFT JOIN dc_fund_collect b " \
        #       "ON b.fund_id = a.id " \
        #       "WHERE a.`status` = '1' AND a.`cn_name` = '龙财兴业基金二期'"

        cursor.execute(sql)
        fundResults1 = cursor.fetchall()
        fields = cursor.description
        compareCvs2FundList(fundResults1, fields)

    db.close()

def compareCvs2FundList(fundResults1, fields):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cvs_prod", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    fundNameStr = ''
    for index in range(len(fundResults1)):
        print(index)
        fundName1 = fundResults1[index].get(u"fundName")
        if fundName1 != None:
            fundName1 = MySQLdb.escape_string(fundName1)
            fundNameStr += ",'"+fundName1+"'"
    print(fundNameStr)
    fundNameStr=fundNameStr[1:]

    #management_money 管理资金总额 对应的就是1.0库中的 finally_scale募集完成规模 字段
    viewFundsql = "SELECT company_id AS fundIdOrCompanyId, full_name AS fundName, source, '' AS info_status, " \
                  "fund_type AS fundType, contact_address_data AS address, description, invest_strategy AS strategyDescribe, " \
                  "collect_start_time AS collectBeginTime, collect_finally_time AS collectFinallyTime, " \
                  "management_money AS collectFinallyScale, collect_status AS collectStatus, lp_data, " \
                  "manager_company_short_names , fund_association, enable_search AS beizhu " \
                  "FROM view_fund WHERE full_name IN (%s) " \
                  "ORDER BY full_name DESC"%fundNameStr

    cursor.execute(viewFundsql)
    viewFundResults2 = cursor.fetchall()
    for index in range(len(fundResults1)):
        fundName1 = fundResults1[index].get(u"fundName")
        #1.0库中注册地址
        regAddress1 = fundResults1[index].get(u"address")
        description1 = fundResults1[index].get(u"description")
        #是否在发改委备案 对应2.0 中的 是否在基金业协会：1:是,2:否'
        fund_association1 = fundResults1[index].get(u"fund_association")
        fundType1 = fundResults1[index].get(u"fundType")
        #cvs2.0 库中 fundType 字典对应的数字代表的值 与 cvs1.0中 相比变了，如下
        if fundType1 == 7:
            fundType1 = 1
        elif fundType1 == 8:
            fundType1 = 2
        elif fundType1 == 9:
            fundType1 =3
        elif fundType1 == 10:
            fundType1 = 4
        elif fundType1 == 13:
            fundType1 = 5
        elif fundType1 == 21:
            fundType1 = 6
        elif fundType1 == 22:
            fundType1 = 7
        elif fundType1 == 23:
            fundType1 = 8
        elif fundType1 == 24:
            fundType1 = 9
        elif fundType1 == 25:
            fundType1 = 10
        elif fundType1 == 26:
            fundType1 = 11

        source1 = fundResults1[index].get(u"source")
        collectBeginTime1 = fundResults1[index].get(u"collectBeginTime")
        if collectBeginTime1 != None:
            #view_fund  表中的基金开始募集时间 只有年月日没有时分秒，所以此处需要格式化
            collectBeginTime1 = datetime.datetime.strftime(collectBeginTime1,'%Y-%m-%d')
        collectFinallyTime1 = fundResults1[index].get(u"collectFinallyTime")
        if collectFinallyTime1 != None:
            collectFinallyTime1 = datetime.datetime.strftime(collectFinallyTime1,'%Y-%m-%d')
        collectFinallyScale1 = fundResults1[index].get(u"collectFinallyScale")
        #2.0数据库 募集完成规模 金额存到分，so 1.0库中的万元需要转换
        if collectFinallyScale1 != None:
            collectFinallyScale1 = int(math.floor(collectFinallyScale1))
            collectFinallyScale1 = collectFinallyScale1*1000000
        collectStatus1 = fundResults1[index].get(u"collectStatus")
        #2.0数据库 只有两个状态1、开始募集；2、募集完成   但是1.0库中有三个 1、开始募集 2、首轮募集 3、募集完成  so需要替换
        if collectStatus1 == 2:
            collectStatus1 = 1
        elif collectStatus1 == 3:
            collectStatus1 = 2
        fundId1 = fundResults1[index].get(u"fundIdOrCompanyId")

        for viewOrgIndex in range(len(viewFundResults2)):
            fundName2= viewFundResults2[viewOrgIndex].get(u"fundName")
            if fundName2 == fundName1:
                source2 = viewFundResults2[viewOrgIndex].get(u"source")
                #2.0中注册地址
                address2 = viewFundResults2[viewOrgIndex].get(u"address")
                description2 = viewFundResults2[viewOrgIndex].get(u"description")
                fund_association2 = viewFundResults2[viewOrgIndex].get(u"fund_association")
                fundType2 = viewFundResults2[viewOrgIndex].get(u"fundType")
                collectBeginTime2 = viewFundResults2[viewOrgIndex].get(u"collectBeginTime")
                if collectBeginTime2 != None and collectBeginTime2 !="":
                    collectBeginTime2 = datetime.datetime.strftime(collectBeginTime2,'%Y-%m-%d')
                collectFinallyTime2 = viewFundResults2[viewOrgIndex].get(u"collectFinallyTime")
                if collectFinallyTime2 != None and collectFinallyTime2 != "":
                    collectFinallyTime2 = datetime.datetime.strftime(collectFinallyTime2,'%Y-%m-%d')
                collectFinallyScale2 = viewFundResults2[viewOrgIndex].get(u"collectFinallyScale")
                collectStatus2 = viewFundResults2[viewOrgIndex].get(u"collectStatus")
                if source1 != source2:
                     beizhuStr1 = u'cvs1和2中view_fund 资本来源 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 资本来源 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if regAddress1 != None and regAddress1 !="" and regAddress1 != "-":
                    #如果1.0中地址有数据，但是2.0中没有，则直接记录问题
                    if address2 == None or address2 == "[]":
                        beizhuStr1 = u'cvs1和2中view_fund 详细地址 不完全匹配，此为cvs1中数据'
                        beizhuStr2 = u'cvs1和2中view_fund 详细地址 不完全匹配，此为cvs2中数据'
                        writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                    else:
                        isRegAddress = regAddress1 in address2
                        if isRegAddress != True:
                                 beizhuStr1 = u'cvs1和2中view_fund 详细地址 不完全匹配，此为cvs1中数据'
                                 beizhuStr2 = u'cvs1和2中view_fund 详细地址 不完全匹配，此为cvs2中数据'
                                 writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if description1 != description2:
                     beizhuStr1 = u'cvs1和2中view_fund 基金简介 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 基金简介 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if fund_association1 != fund_association2:
                     beizhuStr1 = u'cvs1和2中view_fund 是否基金业协会备案 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 是否基金业协会备案 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if fundType1 != fundType2:
                     beizhuStr1 = u'cvs1和2中view_fund 基金类型 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 基金类型 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if collectBeginTime1 != collectBeginTime2:
                     beizhuStr1 = u'cvs1和2中view_fund 开始募集时间 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 开始募集时间 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if collectFinallyTime1 != collectFinallyTime2:
                     beizhuStr1 = u'cvs1和2中view_fund 募集完成时间 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 募集完成时间 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                if collectFinallyScale1 != collectFinallyScale2:
                     beizhuStr1 = u'cvs1和2中view_fund 募集完成规模 不完全匹配，此为cvs1中数据'
                     beizhuStr2 = u'cvs1和2中view_fund 募集完成规模 不完全匹配，此为cvs2中数据'
                     writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)
                # if collectStatus1 != collectStatus2:
                #      beizhuStr1 = u'cvs1和2中view_fund 募集状态 不完全匹配，此为cvs1中数据'
                #      beizhuStr2 = u'cvs1和2中view_fund 募集状态 不完全匹配，此为cvs2中数据'
                #      writeData(fundResults1[index], fields, beizhuStr1, viewFundResults2[viewOrgIndex], beizhuStr2)

                #判断‘策略描述’ 是否一致
                strategyDescribe2 = viewFundResults2[viewOrgIndex].get(u"strategyDescribe")
                compareCvs1and2FundStrategyCnDesc(fundResults1[index], fields, fundId1, strategyDescribe2, viewFundResults2[viewOrgIndex])

                #判断 管理公司 是否一致
                # lp_data2 = viewFundResults2[viewOrgIndex].get(u"lp_data")
                # manager_company_short_names2 = viewFundResults2[viewOrgIndex].get(u"manager_company_short_names")
                # compareCvs1and2FundManageCompany(fundResults1[index], fields, fundId1, lp_data2, viewFundResults2[viewOrgIndex],manager_company_short_names2)
            else:
                continue

    db.close()

def compareAddress(orgResults1, fields, regAddress1, viewOrgResults2, address2,orgId1):

    if regAddress1 != None and regAddress1 !='':
        #如果1.0中有注册地址，2.0中 注册地址为空 则有问题直接记录问题数据
        if address2 == None or address2 == "[]":
            address2 = "None"
            beizhuStr1=u'cvs2中此基金缺失此条注册地址，此为cvs1中注册地址：' + regAddress1
            beizhuStr2=u'cvs2中此基金失此条注册地址，此为cvs2中注册地址：' + address2
            writeData(orgResults1, fields, beizhuStr1, viewOrgResults2, beizhuStr2)
            return "Addressbreak"
        else:
            #判断2.0库中是否包含1.0的注册地址,如果不包含，直接记录问题
            isRegAddress = regAddress1 in address2
            if isRegAddress != True:
                beizhuStr1=u'cvs2中此基金缺失此条注册地址，此为cvs1中注册地址：' + regAddress1
                beizhuStr2=u'cvs2中此基金缺失此条注册地址，此为cvs2中注册地址：' + address2
                writeData(orgResults1, fields, beizhuStr1, viewOrgResults2, beizhuStr2)
                return "Addressbreak"

def compareCvs1and2FundStrategyCnDesc(fundResults1, fields, fundId1, strategyDescribe2, viewFundResults2):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT b.strategy_cn_desc AS strategyDescribe " \
          "FROM dc_fund a, dc_fund_strategy b " \
          "WHERE a.id = b.fund_id AND a.`status`=1 AND b.strategy_cn_desc IS NOT NULL " \
          "AND b.strategy_cn_desc !='' AND a.id='%s'" % fundId1

    cursor.execute(sql)
    fundStrategyDescResults1 = cursor.fetchall()
    for index in range(len(fundStrategyDescResults1)):
        strategyDescribe1 = fundStrategyDescResults1[index].get(u"strategyDescribe")
        # strategyDescribe2 = viewOrgResults2.get(u"address")
        if strategyDescribe1!= None:
            #如果1.0中策略描述有数据，但是2.0库中的策略描述为空 则直接记录数据问题
            if strategyDescribe2 == None or strategyDescribe2 == "":
                strategyDescribe2 = "None"
                beizhuStr1=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs1中投资策略：' + strategyDescribe1
                beizhuStr2=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs2中投资策略：' + strategyDescribe2
                writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                return "strategyDescribebreak"
            else:
                #判断2.0库中是否包含1.0库中的策略描述
                isStrategyDescribe = strategyDescribe1 in strategyDescribe2
                if isStrategyDescribe == False:

                    beizhuStr1=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs1中投资策略：' + strategyDescribe1
                    beizhuStr2=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs2中投资策略：' + strategyDescribe2
                    writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    return "strategyDescribebreak"

    db.close()

#地区用的不是fund表里的地区也不是company表里的地区，用的是是天眼查的，目前没对
def compareCvs1and2FundAreaTpye(fundResults1, fields, fundId1, strategyDescribe2, viewFundResults2):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT a.cn_name, a.area_type, b.id, b.cn_name,b.full_name " \
          "FROM dc_fund a, dict_area b " \
          "WHERE a.area_type = b.id AND a.`status`=1  AND a.id='%s'" % fundId1

    cursor.execute(sql)
    fundAreaTypeResults1 = cursor.fetchall()
    for index in range(len(fundAreaTypeResults1)):
        strategyDescribe1 = fundAreaTypeResults1[index].get(u"strategyDescribe")
        # strategyDescribe2 = viewOrgResults2.get(u"address")
        if strategyDescribe1!= None:
            #如果1.0中策略描述有数据，但是2.0库中的策略描述为空 则直接记录数据问题
            if strategyDescribe2 == None or strategyDescribe2 == "":

                strategyDescribe2 = "None"
                beizhuStr1=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs1中投资策略：' + strategyDescribe1
                beizhuStr2=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs2中投资策略：' + strategyDescribe2
                writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                return "strategyDescribebreak"
            else:
                #判断2.0库中是否包含1.0库中的策略描述
                isStrategyDescribe = strategyDescribe1 in strategyDescribe2
                if isStrategyDescribe == False:

                    beizhuStr1=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs1中投资策略：' + strategyDescribe1
                    beizhuStr2=u'cvs2中此基金投资策略与cvs1中不匹配，此为cvs2中投资策略：' + strategyDescribe2
                    writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    return "strategyDescribebreak"

    db.close()

def compareCvs1and2FundManageCompany(fundResults1, fields, fundId1, lp_data2, viewFundResults2,manager_company_short_names2):
    # db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
    #                      cursorclass=MySQLdb.cursors.DictCursor)
    db = MySQLdb.connect("39.106.211.74", "read", "aaKcKA6iDXyJVeF6", "datacenter_online", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    manageCompanySql = "SELECT b.context_type, b.context_id,a.id AS fundId " \
                       "FROM dc_fund a, dc_fund_manage_company b " \
                       "WHERE a.id = b.fund_id AND a.`status` = 1 AND a.id='%s'" % fundId1

    cursor.execute(manageCompanySql)
    fundManageCompanyResults1 = cursor.fetchall()
    for index in range(len(fundManageCompanyResults1)):
        context_type1 = fundManageCompanyResults1[index].get(u"context_type")
        context_id1 = fundManageCompanyResults1[index].get(u"context_id")
        #context_type == 2代表Lp，context_type == 1 代表机构
        if context_type1 ==2:
            fundLpSql = "SELECT a.`name` AS lpName, a.lp_type, b.cn_short " \
                        "FROM dc_lp a,dc_company b " \
                        "WHERE a.company_id = b.id AND a.`status` = 1 AND a.id='%s'" % context_id1
            cursor.execute(fundLpSql)
            fundLpResults1 = cursor.fetchall()
            for index in range(len(fundLpResults1)):
                lpName1 = fundLpResults1[index].get(u"lpName")
                lp_type1 = fundLpResults1[index].get(u"lp_type")
                cn_short1 = fundLpResults1[index].get(u"cn_short")
                if lpName1 != None:
                    #如果1.0中lp有数据，但是2.0库中的lp为空 则直接记录数据问题
                    if lp_data2 == None or lp_data2 == "[]":
                        lp_data2 = "[]"
                        beizhuStr1=u'cvs2中此基金的 LP名称 与cvs1中不匹配，此为cvs1中数据：' + lpName1
                        beizhuStr2=u'cvs2中此基金的 LP名称 与cvs1中不匹配，此为cvs2中数据：' + lp_data2
                        writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    else:
                        #判断 lpname 在2.0的lpdata中是否包含
                        isLpdateName = lpName1 in lp_data2
                        if isLpdateName == False:
                            beizhuStr1=u'cvs2中此基金 LP名称 与cvs1中不匹配，此为cvs1中LP名称：' + lpName1
                            beizhuStr2=u'cvs2中此基金 LP名称 与cvs1中不匹配，此为cvs2中LP数据：' + lp_data2
                            writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)

                if lp_type1 != None:
                    lp_type1 = str(lp_type1)
                     #如果1.0中lp有数据，但是2.0库中的lp为空 则直接记录数据问题
                    if lp_data2 == None or lp_data2 == "[]":
                        lp_data2 = "[]"
                        beizhuStr1=u'cvs2中此基金的 LP类型 与cvs1中不匹配，此为cvs1中数据：' + lp_type1
                        beizhuStr2=u'cvs2中此基金的 LP类型 与cvs1中不匹配，此为cvs2中数据：' + lp_data2
                        writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    else:
                        #判断 lp_type 在2.0的lpdata中是否包含
                        isLpdateTpye = lp_type1 in lp_data2
                        if isLpdateName == False:
                            beizhuStr1=u'cvs2中此基金 LP类型 与cvs1中不匹配，此为cvs1中LP名称：' + lp_type1
                            beizhuStr2=u'cvs2中此基金 LP类型 与cvs1中不匹配，此为cvs2中LP数据：' + lp_data2
                            writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                if cn_short1 != None:
                    #如果1.0中基金的管理公司中lp对应的company简称有数据，但是2.0库中的为空 则直接记录数据问题
                    if manager_company_short_names2 == None or manager_company_short_names2 == "[]":
                        manager_company_short_names2 = "[]"
                        beizhuStr1=u'cvs2中此基金的 管理公司 与cvs1中不匹配，此为cvs1中数据：' + cn_short1
                        beizhuStr2=u'cvs2中此基金的 管理公司 与cvs1中不匹配，此为cvs2中数据：' + manager_company_short_names2
                        writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    else:
                        #判断 cn_short1 在2.0的 管理公司 中是否包含
                        isManagerConpany = cn_short1 in manager_company_short_names2
                        if isManagerConpany == False:
                            beizhuStr1=u'cvs2中此基金 管理公司 与cvs1中不匹配，此为cvs1中LP名称：' + cn_short1
                            beizhuStr2=u'cvs2中此基金 管理公司 与cvs1中不匹配，此为cvs2中LP数据：' + manager_company_short_names2
                            writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)

        if context_type1 == 1:
            fundOrgSql = "SELECT a.`name` AS orgName,b.cn_short " \
                         "FROM dc_organization a,dc_company b " \
                         "WHERE a.company_id = b.id AND a.`status` = 1 AND a.id='%s'" % context_id1
            cursor.execute(fundOrgSql)
            fundOrgResults1 = cursor.fetchall()
            for index in range(len(fundOrgResults1)):
                cn_short1 = fundOrgResults1[index].get(u"cn_short")
                if cn_short1 != None:
                    #如果1.0中基金的管理公司中机构有数据，但是2.0库中的为空 则直接记录数据问题
                    if manager_company_short_names2 == None or manager_company_short_names2 == "[]":
                        manager_company_short_names2 = "[]"
                        beizhuStr1=u'cvs2中此基金的 管理公司 与cvs1中不匹配，此为cvs1中数据：' + cn_short1
                        beizhuStr2=u'cvs2中此基金的 管理公司 与cvs1中不匹配，此为cvs2中数据：' + manager_company_short_names2
                        writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)
                    else:
                        #判断 cn_short1 在2.0的 管理公司 中是否包含
                        isManagerConpany = cn_short1 in manager_company_short_names2
                        if isManagerConpany == False:
                            beizhuStr1=u'cvs2中此基金 管理公司 与cvs1中不匹配，此为cvs1中LP名称：' + cn_short1
                            beizhuStr2=u'cvs2中此基金 管理公司 与cvs1中不匹配，此为cvs2中LP数据：' + manager_company_short_names2
                            writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2)

    db.close()

def writeData(fundResults1, fields, beizhuStr1, viewFundResults2, beizhuStr2):
     fundResults1.update(beizhu=beizhuStr1)
     writeExcel(fundResults1, fields)
     viewFundResults2.update(beizhu=beizhuStr2)
     writeExcel(viewFundResults2, fields)


def writeExcel(results,fields):

    fileName = 'dataViewFund.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
